{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>包裹数量</th>\n",
       "      <th>实际重量</th>\n",
       "      <th>计费重量</th>\n",
       "      <th>应收费用</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>出口日期</th>\n",
       "      <th>账单日期</th>\n",
       "      <th>账单号码</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-09-05</th>\n",
       "      <th>2018-10-04</th>\n",
       "      <th>100010386758</th>\n",
       "      <td>5</td>\n",
       "      <td>84.0</td>\n",
       "      <td>92.5</td>\n",
       "      <td>3092.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-06</th>\n",
       "      <th>2018-10-04</th>\n",
       "      <th>100010386758</th>\n",
       "      <td>8</td>\n",
       "      <td>218.0</td>\n",
       "      <td>224.0</td>\n",
       "      <td>7620.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-07</th>\n",
       "      <th>2018-10-04</th>\n",
       "      <th>100010386758</th>\n",
       "      <td>19</td>\n",
       "      <td>295.0</td>\n",
       "      <td>313.5</td>\n",
       "      <td>10363.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-08</th>\n",
       "      <th>2018-10-04</th>\n",
       "      <th>100010386758</th>\n",
       "      <td>13</td>\n",
       "      <td>320.0</td>\n",
       "      <td>340.5</td>\n",
       "      <td>14923.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-11</th>\n",
       "      <th>2018-09-26</th>\n",
       "      <th>100010354890</th>\n",
       "      <td>10</td>\n",
       "      <td>156.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>1462.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-27</th>\n",
       "      <th>2019-01-09</th>\n",
       "      <th>100010711793</th>\n",
       "      <td>14</td>\n",
       "      <td>414.5</td>\n",
       "      <td>418.5</td>\n",
       "      <td>2259.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <th>2019-01-03</th>\n",
       "      <th>100010685264</th>\n",
       "      <td>15</td>\n",
       "      <td>307.5</td>\n",
       "      <td>429.0</td>\n",
       "      <td>14750.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-29</th>\n",
       "      <th>2019-01-03</th>\n",
       "      <th>100010685264</th>\n",
       "      <td>2</td>\n",
       "      <td>30.0</td>\n",
       "      <td>40.5</td>\n",
       "      <td>1411.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-02</th>\n",
       "      <th>2019-01-14</th>\n",
       "      <th>100010723210</th>\n",
       "      <td>17</td>\n",
       "      <td>337.5</td>\n",
       "      <td>469.5</td>\n",
       "      <td>2466.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-09</th>\n",
       "      <th>2019-01-21</th>\n",
       "      <th>100010737576</th>\n",
       "      <td>2</td>\n",
       "      <td>28.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>90.43</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>106 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    包裹数量   实际重量   计费重量      应收费用\n",
       "出口日期       账单日期       账单号码                                      \n",
       "2018-09-05 2018-10-04 100010386758     5   84.0   92.5   3092.91\n",
       "2018-09-06 2018-10-04 100010386758     8  218.0  224.0   7620.22\n",
       "2018-09-07 2018-10-04 100010386758    19  295.0  313.5  10363.53\n",
       "2018-09-08 2018-10-04 100010386758    13  320.0  340.5  14923.59\n",
       "2018-09-11 2018-09-26 100010354890    10  156.0  169.0   1462.26\n",
       "...                                  ...    ...    ...       ...\n",
       "2018-12-27 2019-01-09 100010711793    14  414.5  418.5   2259.63\n",
       "2018-12-28 2019-01-03 100010685264    15  307.5  429.0  14750.15\n",
       "2018-12-29 2019-01-03 100010685264     2   30.0   40.5   1411.42\n",
       "2019-01-02 2019-01-14 100010723210    17  337.5  469.5   2466.01\n",
       "2019-01-09 2019-01-21 100010737576     2   28.0   39.0     90.43\n",
       "\n",
       "[106 rows x 4 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "file=\"运费明细表.xlsx\"\n",
    "df01=pd.read_excel(file,sheep_name=\"运费明细\")\n",
    "df03=df01.groupby([\"出口日期\",\"账单日期\",\"账单号码\"]).sum()\n",
    "df03\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>出口日期</th>\n",
       "      <th>账单日期</th>\n",
       "      <th>账单号码</th>\n",
       "      <th>包裹数量</th>\n",
       "      <th>实际重量</th>\n",
       "      <th>计费重量</th>\n",
       "      <th>应收费用</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-09-05</td>\n",
       "      <td>2018-10-04</td>\n",
       "      <td>100010386758</td>\n",
       "      <td>5</td>\n",
       "      <td>84.0</td>\n",
       "      <td>92.5</td>\n",
       "      <td>3092.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-09-06</td>\n",
       "      <td>2018-10-04</td>\n",
       "      <td>100010386758</td>\n",
       "      <td>8</td>\n",
       "      <td>218.0</td>\n",
       "      <td>224.0</td>\n",
       "      <td>7620.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-09-07</td>\n",
       "      <td>2018-10-04</td>\n",
       "      <td>100010386758</td>\n",
       "      <td>19</td>\n",
       "      <td>295.0</td>\n",
       "      <td>313.5</td>\n",
       "      <td>10363.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-09-08</td>\n",
       "      <td>2018-10-04</td>\n",
       "      <td>100010386758</td>\n",
       "      <td>13</td>\n",
       "      <td>320.0</td>\n",
       "      <td>340.5</td>\n",
       "      <td>14923.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-09-11</td>\n",
       "      <td>2018-09-26</td>\n",
       "      <td>100010354890</td>\n",
       "      <td>10</td>\n",
       "      <td>156.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>1462.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>2018-12-27</td>\n",
       "      <td>2019-01-09</td>\n",
       "      <td>100010711793</td>\n",
       "      <td>14</td>\n",
       "      <td>414.5</td>\n",
       "      <td>418.5</td>\n",
       "      <td>2259.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>2018-12-28</td>\n",
       "      <td>2019-01-03</td>\n",
       "      <td>100010685264</td>\n",
       "      <td>15</td>\n",
       "      <td>307.5</td>\n",
       "      <td>429.0</td>\n",
       "      <td>14750.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>2018-12-29</td>\n",
       "      <td>2019-01-03</td>\n",
       "      <td>100010685264</td>\n",
       "      <td>2</td>\n",
       "      <td>30.0</td>\n",
       "      <td>40.5</td>\n",
       "      <td>1411.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>2019-01-02</td>\n",
       "      <td>2019-01-14</td>\n",
       "      <td>100010723210</td>\n",
       "      <td>17</td>\n",
       "      <td>337.5</td>\n",
       "      <td>469.5</td>\n",
       "      <td>2466.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>2019-01-09</td>\n",
       "      <td>2019-01-21</td>\n",
       "      <td>100010737576</td>\n",
       "      <td>2</td>\n",
       "      <td>28.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>90.43</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>106 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          出口日期       账单日期          账单号码  包裹数量   实际重量   计费重量      应收费用\n",
       "0   2018-09-05 2018-10-04  100010386758     5   84.0   92.5   3092.91\n",
       "1   2018-09-06 2018-10-04  100010386758     8  218.0  224.0   7620.22\n",
       "2   2018-09-07 2018-10-04  100010386758    19  295.0  313.5  10363.53\n",
       "3   2018-09-08 2018-10-04  100010386758    13  320.0  340.5  14923.59\n",
       "4   2018-09-11 2018-09-26  100010354890    10  156.0  169.0   1462.26\n",
       "..         ...        ...           ...   ...    ...    ...       ...\n",
       "101 2018-12-27 2019-01-09  100010711793    14  414.5  418.5   2259.63\n",
       "102 2018-12-28 2019-01-03  100010685264    15  307.5  429.0  14750.15\n",
       "103 2018-12-29 2019-01-03  100010685264     2   30.0   40.5   1411.42\n",
       "104 2019-01-02 2019-01-14  100010723210    17  337.5  469.5   2466.01\n",
       "105 2019-01-09 2019-01-21  100010737576     2   28.0   39.0     90.43\n",
       "\n",
       "[106 rows x 7 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "file=\"运费明细表.xlsx\"\n",
    "df01=pd.read_excel(file,sheep_name=\"运费明细\")\n",
    "df03=df01.groupby([\"出口日期\",\"账单日期\",\"账单号码\"],as_index=False).sum()\n",
    "df03\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "出口日期\n",
       "2018-09-05     3092.91\n",
       "2018-09-06     7620.22\n",
       "2018-09-07    10363.53\n",
       "2018-09-08    14923.59\n",
       "2018-09-11     6803.08\n",
       "2018-09-12     5088.56\n",
       "2018-09-13     7317.97\n",
       "2018-09-14      281.23\n",
       "2018-09-18     2380.25\n",
       "2018-09-20    10634.62\n",
       "2018-09-25     2902.11\n",
       "2018-09-26      713.73\n",
       "2018-09-27    21173.72\n",
       "2018-09-29     4393.94\n",
       "2018-10-03     2704.19\n",
       "2018-10-04    -3283.70\n",
       "2018-10-08    19978.33\n",
       "2018-10-10     1703.64\n",
       "2018-10-12     5318.65\n",
       "2018-10-13    11813.71\n",
       "dtype: float64"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "file=\"运费明细表.xlsx\"\n",
    "df01=pd.read_excel(file,sheep_name=\"运费明细\")\n",
    "df02=df01.groupby(\"出口日期\").apply(lambda x:sum(x[\"应收费用\"]))##一定要带apply,不然不能显示,\n",
    "df02.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>账单号码</th>\n",
       "      <th>应收费用</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>出口日期</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-09-05</th>\n",
       "      <td>3</td>\n",
       "      <td>3092.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-06</th>\n",
       "      <td>7</td>\n",
       "      <td>7620.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-07</th>\n",
       "      <td>9</td>\n",
       "      <td>10363.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-08</th>\n",
       "      <td>11</td>\n",
       "      <td>14923.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-11</th>\n",
       "      <td>10</td>\n",
       "      <td>6803.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-12</th>\n",
       "      <td>5</td>\n",
       "      <td>5088.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-13</th>\n",
       "      <td>4</td>\n",
       "      <td>7317.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-14</th>\n",
       "      <td>2</td>\n",
       "      <td>281.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-18</th>\n",
       "      <td>1</td>\n",
       "      <td>2380.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-20</th>\n",
       "      <td>1</td>\n",
       "      <td>10634.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-25</th>\n",
       "      <td>4</td>\n",
       "      <td>2902.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-26</th>\n",
       "      <td>2</td>\n",
       "      <td>713.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-27</th>\n",
       "      <td>1</td>\n",
       "      <td>21173.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-29</th>\n",
       "      <td>1</td>\n",
       "      <td>4393.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-03</th>\n",
       "      <td>2</td>\n",
       "      <td>2704.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-04</th>\n",
       "      <td>2</td>\n",
       "      <td>-3283.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-08</th>\n",
       "      <td>1</td>\n",
       "      <td>19978.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-10</th>\n",
       "      <td>1</td>\n",
       "      <td>1703.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-12</th>\n",
       "      <td>1</td>\n",
       "      <td>5318.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-13</th>\n",
       "      <td>7</td>\n",
       "      <td>11813.71</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            账单号码      应收费用\n",
       "出口日期                      \n",
       "2018-09-05     3   3092.91\n",
       "2018-09-06     7   7620.22\n",
       "2018-09-07     9  10363.53\n",
       "2018-09-08    11  14923.59\n",
       "2018-09-11    10   6803.08\n",
       "2018-09-12     5   5088.56\n",
       "2018-09-13     4   7317.97\n",
       "2018-09-14     2    281.23\n",
       "2018-09-18     1   2380.25\n",
       "2018-09-20     1  10634.62\n",
       "2018-09-25     4   2902.11\n",
       "2018-09-26     2    713.73\n",
       "2018-09-27     1  21173.72\n",
       "2018-09-29     1   4393.94\n",
       "2018-10-03     2   2704.19\n",
       "2018-10-04     2  -3283.70\n",
       "2018-10-08     1  19978.33\n",
       "2018-10-10     1   1703.64\n",
       "2018-10-12     1   5318.65\n",
       "2018-10-13     7  11813.71"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "file=\"运费明细表.xlsx\"\n",
    "df01=pd.read_excel(file,sheep_name=\"运费明细\")\n",
    "df02=df01.groupby(\"出口日期\").agg({\"账单号码\":\"count\",\"应收费用\":\"sum\"})\n",
    "df02.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>应收费用</th>\n",
       "      <th>level_1</th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3092.91</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>84.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7620.22</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>218.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10363.53</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>295.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14923.59</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>320.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6803.08</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>296.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5088.56</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>126.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7317.97</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>193.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>281.23</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2380.25</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>92.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10634.62</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>327.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2902.11</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>84.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>713.73</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>327.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>21173.72</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>350.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>4393.94</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>95.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2704.19</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>445.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>-3283.70</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>19978.33</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>731.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1703.64</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>731.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>5318.65</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>165.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>11813.71</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>293.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>25320.93</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>850.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>32313.16</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>1076.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>22605.46</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>1069.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>27448.77</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>1427.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>11978.31</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>309.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>538.21</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>7442.47</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>360.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>17808.03</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>553.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>16611.71</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>577.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>4639.72</td>\n",
       "      <td>实际重量</td>\n",
       "      <td>193.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        应收费用 level_1       0\n",
       "0    3092.91    实际重量    84.0\n",
       "1    7620.22    实际重量   218.0\n",
       "2   10363.53    实际重量   295.0\n",
       "3   14923.59    实际重量   320.0\n",
       "4    6803.08    实际重量   296.5\n",
       "5    5088.56    实际重量   126.0\n",
       "6    7317.97    实际重量   193.0\n",
       "7     281.23    实际重量    42.0\n",
       "8    2380.25    实际重量    92.0\n",
       "9   10634.62    实际重量   327.0\n",
       "10   2902.11    实际重量    84.0\n",
       "11    713.73    实际重量   327.5\n",
       "12  21173.72    实际重量   350.0\n",
       "13   4393.94    实际重量    95.0\n",
       "14   2704.19    实际重量   445.0\n",
       "15  -3283.70    实际重量     0.0\n",
       "16  19978.33    实际重量   731.0\n",
       "17   1703.64    实际重量   731.0\n",
       "18   5318.65    实际重量   165.0\n",
       "19  11813.71    实际重量   293.0\n",
       "20  25320.93    实际重量   850.0\n",
       "21  32313.16    实际重量  1076.0\n",
       "22  22605.46    实际重量  1069.5\n",
       "23  27448.77    实际重量  1427.5\n",
       "24  11978.31    实际重量   309.0\n",
       "25    538.21    实际重量    45.0\n",
       "26   7442.47    实际重量   360.5\n",
       "27  17808.03    实际重量   553.5\n",
       "28  16611.71    实际重量   577.0\n",
       "29   4639.72    实际重量   193.0"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "file=\"运费明细表.xlsx\"\n",
    "df01=pd.read_excel(file,sheep_name=\"运费明细\")\n",
    "df02=pd.pivot_table(df01,index=[\"出口日期\"],values=[\"应收费用\",\"实际重量\"],\n",
    "                    aggfunc={\"应收费用\":\"sum\",\"实际重量\":\"sum\"})#columns=[\"账单号码\"],fill_value=0, margins=True,margins_name=\"汇总\"\n",
    "df03=df02.set_index([\"应收费用\"]).stack().reset_index()            \n",
    "df03.head(30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "file=\"运费明细表.xlsx\"\n",
    "df01=pd.read_excel(file,sheep_name=\"运费明细\")\n",
    "df02=pd.pivot_table(df01,index=[\"出口日期\"],values=[\"应收费用\",\"实际重量\"],columns=[\"账单号码\"],margins=True,margins_name=\"汇总\",\n",
    "                    aggfunc={\"应收费用\":\"sum\",\"实际重量\":\"sum\"})#columns=[\"账单号码\"],fill_value=0, margins=True,margins_name=\"汇总\"\n",
    "df02.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.ExcelWriter(file, mode='a',engine='openpyxl') as writer:\n",
    "    df02.to_excel(writer,sheet_name='001',index=False)  #新开一工作表001而保存文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
